package com.example.dbmeta.meta.jpacolumn;
import com.example.dbmeta.meta.common.xml.XmlConfigLoader;
import com.example.dbmeta.meta.common.xml.DataInstance;
import com.example.dbmeta.meta.common.xml.XmlConfig;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
@Service
@Slf4j
public class MetaColumnService {

    @Autowired
    private ColumnMetaJpaRepository columnRepository;
    @Autowired
    private XmlConfigLoader xmlConfigLoader;
    //连接配置的数据库，具有配置表的信息
    public void getallcolumnsinfo() throws Exception {

        XmlConfig xmlConfig = xmlConfigLoader.loadDataInstancesFromXml("config.xml");
        List<DataInstance> dataInstanceList = xmlConfig.getSourceDataSource().getDataInstances();
        columnRepository.deleteAll();
        log.info("清空表完成");
        List<ColumnMetaInfo> columnMetaInfos = new ArrayList<>();
        for (DataInstance di : dataInstanceList) {
            try {
                //1.加载驱动程序
                Class.forName(di.getDriverclassname());
                //2.获得数据库链接
                Connection conn = DriverManager.getConnection(di.getUrl(), di.getUsername(), di.getPassword());
                //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
                Statement st = conn.createStatement();
                String getfieldsql = "SELECT *\n" +
                        "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA NOT IN (\n" +
                        "    'information_schema',\n" +
                        "    'mysql',\n" +
                        "    'performance_schema',\n" +
                        "    'sys'\n" +
                        ")";
                ResultSet rs = st.executeQuery(getfieldsql);
                // 5. 处理结果集
                while (rs.next()) {
                    ColumnMetaInfo meta = new ColumnMetaInfo();
                    meta.setTableCatalog(rs.getString("TABLE_CATALOG"));
                    meta.setTableSchema(rs.getString("TABLE_SCHEMA"));
                    meta.setTableName(rs.getString("TABLE_NAME"));
                    meta.setColumnName(rs.getString("COLUMN_NAME"));
                    meta.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
                    meta.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
                    meta.setIsNullable(rs.getString("IS_NULLABLE"));
                    meta.setDataType(rs.getString("DATA_TYPE"));
//                    meta.setCharacterMaximumLength(rs.getObject("CHARACTER_MAXIMUM_LENGTH") == null ? null : rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
//                    meta.setCharacterOctetLength(rs.getObject("CHARACTER_OCTET_LENGTH") == null ? null : rs.getInt("CHARACTER_OCTET_LENGTH"));
                    meta.setNumericPrecision(rs.getObject("NUMERIC_PRECISION") == null ? null : rs.getInt("NUMERIC_PRECISION"));
                    meta.setNumericScale(rs.getObject("NUMERIC_SCALE") == null ? null : rs.getInt("NUMERIC_SCALE"));
                    meta.setDatetimePrecision(rs.getObject("DATETIME_PRECISION") == null ? null : rs.getInt("DATETIME_PRECISION"));
                    meta.setCharacterSetName(rs.getString("CHARACTER_SET_NAME"));
                    meta.setCollationName(rs.getString("COLLATION_NAME"));
                    meta.setColumnType(rs.getString("COLUMN_TYPE"));
                    meta.setColumnKey(rs.getString("COLUMN_KEY"));
                    meta.setExtra(rs.getString("EXTRA"));
                    meta.setPrivileges(rs.getString("PRIVILEGES"));
                    meta.setColumnComment(rs.getString("COLUMN_COMMENT"));
                    meta.setGenerationExpression(rs.getString("GENERATION_EXPRESSION"));
                    meta.setSrsId(rs.getString("SRS_ID"));

                    meta.setDbname(di.getDbinstancename());
                    meta.setNote(di.getNote());
                    columnMetaInfos.add(meta);
                }

                分批量保存(di, columnMetaInfos);
                columnMetaInfos.clear();
                //关闭资源
                rs.close();
                st.close();
                conn.close();
            } catch (Exception ex) {
                System.out.println(ex);
            }
            //每一次datainstance，加一个分割线
        log.info("----------------------------------------------------------");
        }
        log.info("共计{}实例,当前已经保存结束",dataInstanceList.size());
        //统一对List进行存储
    }

//    private void 分批量保存(DataInstance di,List<ColumnMeta> columnMetas) {
//        int totalBatches = 100;
//        int listSize = columnMetas.size();
//        int batchSize = (listSize + totalBatches - 1) / totalBatches; // 向上取整
//        int batchNumber = 0; // 新增一个批次计数器
//        for (int i = 0; i < columnMetas.size(); i += batchSize) {
//            int end = Math.min(i + batchSize, listSize);
//            List<ColumnMeta> batch = columnMetas.subList(i, end);
//            columnRepository.saveAll(batch);
//            batchNumber++; // 每处理一批就加1
////            log.info("当前正在保存的是第{}批，总共{}批次", batchNumber, totalBatches);
//            log.info("当前正在保存的是第{}数据实例，{}/{}批，共{}条数据，数据范围: {} - {}",di.getDbinstancename(),
//                    batchNumber, totalBatches, batch.size(), i, end);
//        }
//    }
    private void 分批量保存(DataInstance di, List<ColumnMetaInfo> columnMetaInfos) {
        final int BATCH_SIZE = 1000;  // 固定每批次500条记录
        int listSize = columnMetaInfos.size();

        // 处理空列表情况
        if (listSize == 0) {
            log.warn("数据实例[{}]的ColumnMeta列表为空, 跳过保存", di.getDbinstancename());
            return;
        }

        // 计算实际需要的批次数量
        int totalBatches = (listSize + BATCH_SIZE - 1) / BATCH_SIZE;  // 向上取整

        log.info("开始保存数据实例[{}]: 总计{}条数据，需要分{}批次保存（每批{}条）",
                di.getDbinstancename(), listSize, totalBatches, BATCH_SIZE);

        for (int batchIndex = 0; batchIndex < totalBatches; batchIndex++) {
            int start = batchIndex * BATCH_SIZE;
            int end = Math.min(start + BATCH_SIZE, listSize);
            List<ColumnMetaInfo> batch = columnMetaInfos.subList(start, end);

            try {
                columnRepository.saveAll(batch);

                // 优化后的日志显示更清晰的索引范围
                log.info("保存成功: 实例[{}] | 批次 {}/{} | 本批{}条 | 索引范围: [{}~{}]",
                        di.getDbinstancename(),
                        batchIndex + 1,
                        totalBatches,
                        batch.size(),
                        start,
                        end - 1);  // 修正结束索引为实际包含的最后位置

            } catch (DataAccessException e) {
                log.error("保存失败: 实例[{}] | 批次 {}/{} | 索引[{}~{}] | 错误: {}",
                        di.getDbinstancename(),
                        batchIndex + 1,
                        totalBatches,
                        start,
                        end - 1,
                        e.getMessage());
                // 可选的失败处理逻辑: 重试/记录失败数据等
            }
        }

        log.info("数据实例[{}]保存完成! 总记录数: {}, 成功批次: {}/{}",
                di.getDbinstancename(), listSize, totalBatches, totalBatches);
    }

}